﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp2
{
    public class DBClass
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(DBClass));
        Database DB;
        public DBClass()
        {
            DB = (new DbContext("name=WeightDB")).Database;
        }

        public Test GetQuery()
        {
           return  DB.SqlQuery<Test>("SELECT ID,Name,Age,IsClose,Count FROM dbo.Test").ToList().FirstOrDefault();
        }

        public int Add(Test t)
        {
            var cmd = @"INSERT dbo.Test (ID,Name,Age,IsClose,Count)
                        VALUES  (@ID,@Name,@Age,@IsClose,@Count)";
          return  DB.ExecuteSqlCommand(cmd,
                new SqlParameter("@ID", t.ID),
                new SqlParameter("@Name", t.Name),
                new SqlParameter("@Age", t.Age),
                new SqlParameter("@IsClose", t.IsClose),
                new SqlParameter("@Count", t.Count));
        }
        public int Update(Test t)
        {
            var cmd = @"
                    UPDATE dbo.Test
                    SET Name=@Name,Age=@Age,IsClose=@IsClose,Count=@Count
                    WHERE ID=@ID";
            return DB.ExecuteSqlCommand(cmd,
                new SqlParameter("@Name", t.Name),
                new SqlParameter("@Count", t.Count),
                new SqlParameter("@Age", t.Age),
                new SqlParameter("@IsClose", t.IsClose));
        }

        public Weight1 GetWeight()
        {
            var cmd = @"SELECT ID,[LineNo],Stage,TrayCode,BattBarCode,FInjectionWeight,
                       BInjectionWeight,InjectionWeight,IsGoods,DevValue,Range,FScanTime,
                       FWeightTime,BWeightTime,RepairInjectionWeight,
                       RepairInjectionWeightTime,BRepairInjectionWeight,TheoryRepairValue,
                       ActualRepairValue,Finished	 FROM dbo.Weight";
           return DB.SqlQuery<Weight1>(cmd).ToList().FirstOrDefault();
        }

        public int AddWeight(Weight1 w)
        {
            var cmd = @"INSERT dbo.Weight (ID,[LineNo],Stage,TrayCode,BattBarCode,
                         FInjectionWeight,BInjectionWeight,InjectionWeight,
                         IsGoods,DevValue,Range,FScanTime,FWeightTime,
                         BWeightTime,RepairInjectionWeight,
                         RepairInjectionWeightTime,BRepairInjectionWeight,
                         TheoryRepairValue,ActualRepairValue,Finished)
                      VALUES  ( @ID,@LineNo,@Stage,@TrayCode,@BattBarCode,
                         @FInjectionWeight,@BInjectionWeight,@InjectionWeight,
                         @IsGoods,@DevValue,@Range,@FScanTime,@FWeightTime,
                         @BWeightTime,@RepairInjectionWeight,
                         @RepairInjectionWeightTime,@BRepairInjectionWeight,
                         @TheoryRepairValue,@ActualRepairValue,@Finished )";
            return DB.ExecuteSqlCommand(cmd,
                  new SqlParameter("@ID", w.ID),
                  new SqlParameter("@LineNo", w.LineNo == null ? DBNull.Value : (object)w.LineNo),
                  new SqlParameter("@Stage", w.Stage == null ? DBNull.Value : (object)w.Stage),
                  new SqlParameter("@TrayCode", w.TrayCode == null ? DBNull.Value : (object)w.TrayCode),
                  new SqlParameter("@BattBarCode", w.BattBarCode == null ? DBNull.Value : (object)w.BattBarCode),
                  new SqlParameter("@FInjectionWeight", w.FInjectionWeight == null ? DBNull.Value : (object)w.FInjectionWeight),
                  new SqlParameter("@BInjectionWeight", w.BInjectionWeight == null ? DBNull.Value : (object)w.BInjectionWeight),
                  new SqlParameter("@InjectionWeight", w.InjectionWeight == null ? DBNull.Value : (object)w.InjectionWeight),
                  new SqlParameter("@IsGoods", w.IsGoods == null ? DBNull.Value : (object)w.IsGoods),
                  new SqlParameter("@DevValue", w.DevValue == null ? DBNull.Value : (object)w.DevValue),
                  new SqlParameter("@Range", w.Range == null ? DBNull.Value : (object)w.Range),
                  new SqlParameter("@FScanTime", w.FScanTime == null ? DBNull.Value : (object)w.FScanTime),
                  new SqlParameter("@FWeightTime", w.FWeightTime == null ? DBNull.Value : (object)w.FWeightTime),
                  new SqlParameter("@BWeightTime", w.BWeightTime == null ? DBNull.Value : (object)w.BWeightTime),
                  new SqlParameter("@RepairInjectionWeight", w.RepairInjectionWeight == null ? DBNull.Value : (object)w.RepairInjectionWeight),
                  new SqlParameter("@RepairInjectionWeightTime", w.RepairInjectionWeightTime == null ? DBNull.Value : (object)w.RepairInjectionWeightTime),
                  new SqlParameter("@BRepairInjectionWeight", w.BRepairInjectionWeight == null ? DBNull.Value : (object)w.BRepairInjectionWeight),
                  new SqlParameter("@TheoryRepairValue", w.TheoryRepairValue == null ? DBNull.Value : (object)w.TheoryRepairValue),
                  new SqlParameter("@ActualRepairValue", w.ActualRepairValue == null ? DBNull.Value : (object)w.ActualRepairValue),
                  new SqlParameter("@Finished", w.Finished == null ? DBNull.Value : (object)w.Stage)
                  );
        }

        public int UpdateBattry(Weight1 w)
        {
            var cmd = @"UPDATE dbo.Weight
                        SET LINENO=@LINENO,Stage=@Stage,TrayCode=@TrayCode,BattBarCode=@BattBarCode,
                        FInjectionWeight=@FInjectionWeight,BInjectionWeight=@BInjectionWeight
                        ,InjectionWeight=@InjectionWeight,IsGoods=@IsGoods,DevValue=@DevValue,Range=@Range,
                        FScanTime=@FScanTime,FWeightTime=@FWeightTime,BWeightTime=@BWeightTime,
                        RepairInjectionWeight=@RepairInjectionWeight,RepairInjectionWeightTime=@RepairInjectionWeightTime,
                        BRepairInjectionWeight=@BRepairInjectionWeight,TheoryRepairValue=@TheoryRepairValue,
                        ActualRepairValue=@ActualRepairValue,Finished=@Finished
                        WHERE ID=@ID;";
            return DB.ExecuteSqlCommand(cmd,
                new SqlParameter("@ID", w.ID),
                new SqlParameter("@LineNo", w.LineNo),
                new SqlParameter("@Stage", w.Stage),
                new SqlParameter("@TrayCode", w.TrayCode),
                new SqlParameter("@BattBarCode", w.BattBarCode),
                new SqlParameter("@FInjectionWeight", w.FInjectionWeight),
                new SqlParameter("@BInjectionWeight", w.BInjectionWeight),
                new SqlParameter("@InjectionWeight", w.InjectionWeight),
                new SqlParameter("@IsGoods", w.IsGoods),
                new SqlParameter("@DevValue", w.DevValue),
                new SqlParameter("@Range", w.Range),
                new SqlParameter("@FScanTime", w.FScanTime),
                new SqlParameter("@FWeightTime", w.FWeightTime),
                new SqlParameter("@BWeightTime", w.BWeightTime),
                new SqlParameter("@RepairInjectionWeight", w.RepairInjectionWeight),
                new SqlParameter("@RepairInjectionWeightTime", w.RepairInjectionWeightTime),
                new SqlParameter("@BRepairInjectionWeight", w.BRepairInjectionWeight),
                new SqlParameter("@TheoryRepairValue", w.TheoryRepairValue),
                new SqlParameter("@ActualRepairValue", w.ActualRepairValue),
                new SqlParameter("@Finished", w.Finished)
                );
        }
    }

    public class Test
    {
        public Guid ID { get; set; }
        public string Name { get; set; }
        public  int Age { get; set; }
        public bool IsClose { get; set; }
        public decimal Count { get; set; }             
    }

    //ID,[LineNo],Stage,TrayCode,BattBarCode,FInjectionWeight,
    //   BInjectionWeight,InjectionWeight,IsGoods,DevValue,Range,FScanTime,
    //   FWeightTime,BWeightTime,RepairInjectionWeight,
    //   RepairInjectionWeightTime,BRepairInjectionWeight,TheoryRepairValue,
    //   ActualRepairValue,Finished

    public class Weight1
    {
        public Guid ID { get; set; }
        public string LineNo { get; set; }
        public int? Stage { get; set; }
        public string TrayCode { get; set; }
        public string BattBarCode { get; set; }
        public Decimal? FInjectionWeight { get; set; }
        public Decimal? BInjectionWeight { get; set; }
        public Decimal? InjectionWeight { get; set; }
        public bool? IsGoods { get; set; }
        public Decimal? DevValue { get; set; }
        public string Range { get; set; }
        public DateTime? FScanTime { get; set; }
        public DateTime? FWeightTime { get; set; }
        public DateTime? BWeightTime { get; set; }
        public Decimal? RepairInjectionWeight { get; set; }
        public DateTime? RepairInjectionWeightTime { get; set; }
        public Decimal? BRepairInjectionWeight { get; set; }
        public Decimal? TheoryRepairValue { get; set; }
        public Decimal? ActualRepairValue { get; set; }
        public bool? Finished { get; set; }

    }

}
